package org.eu.bk201sama.db.test

import groovy.sql.Sql

@Grab(group = 'mysql', module = 'mysql-connector-java', version = '5.1.20')
@GrabConfig(systemClassLoader = true)
//define sql conn
Sql db_35_mobileb2cdev = Sql.newInstance("jdbc:mysql://10.3.3.35:3306/mobileb2cdev?characterEncoding=UTF-8", "lyf_ecstore", "hsy5E9o", "com.mysql.jdbc.Driver")
Sql db_122_bumblebee = Sql.newInstance("jdbc:mysql://10.3.3.122:3308/bumblebee?characterEncoding=UTF-8", "root", "111000", "com.mysql.jdbc.Driver")
Sql db_122_trailbreaker = Sql.newInstance("jdbc:mysql://10.3.3.122:3308/trailbreaker?characterEncoding=UTF-8", "root", "111000", "com.mysql.jdbc.Driver")

/*//插入 主表
db_35_mobileb2cdev.eachRow("SELECT pmt_id AS id ,ptype AS type,'none' AS exclude,0 AS group_id,supplier_id AS  seller_id,is_virtual\n" +
        "  ,name,intro,sdate,edate,stime,etime,url,coverimg,porder AS sort,CASE  WHEN dtline IS NULL THEN UNIX_TIMESTAMP() ELSE dtline END AS create_time,CASE  WHEN dtline IS NULL THEN UNIX_TIMESTAMP() ELSE dtline END AS modify_time,case disabled when 1 then 0 else 1 END AS enable,0 AS deleted,aduit from sdb_promotion WHERE ptype = 'goods';",{row->
    def insertSql = "INSERT INTO pmt_promotions VALUES ('${row.id}','${row.type}','${row.exclude}','${row.group_id}','${row.supplier_id}','${row.seller_id}','${row.is_virtual}','${row.name}','${row.intro}','${row.sdate}','${row.edate}','${row.stime}','${row.etime}','${row.url}','${row.coverimg}','${row.sort}','${row.sort}','${row.sort}','${row.create_time}','${row.modify_time}','${row.enable}','${row.deleted}','${row.aduit}')"
    db_122_bumblebee.execute(insertSql)

})
//插入 附表
db_35_mobileb2cdev.eachRow("SELECT NULL AS id, pmt_id,0 AS good_id,0 as product_id,tbn AS product_bn,'' AS goods_name,\n" +
        "              tprice as price,tstore AS store,tnum AS num,\n" +
        "  idnum,soldoutnum,focusnum,case when img is NULL then '' ELSE img END AS image,case  WHEN intro is NULL THEN '' ELSE intro END AS intro,case when `desc` is NULL THEN '' ELSE `desc` END  AS `desc`,porder AS sort,\n" +
        "  UNIX_TIMESTAMP() AS modify_time,UNIX_TIMESTAMP() AS create_time,case disabled when 1 then 0 else 1 END AS enable,is_deleted AS deleted\n" +
        "from sdb_pmt_target WHERE ttype='goods';",{row->
    def insertSql = "INSERT INTO pmt_goods VALUES ('${row.id}','${row.pmt_id}','${row.good_id}','${row.product_id}','${row.product_bn}','${row.goods_name}','${row.price}','${row.store}','${row.idnum}','${row.focusnum}','${row.edate}','${row.stime}','${row.etime}','${row.url}','${row.coverimg}','${row.sort}','${row.sort}','${row.sort}','${row.create_time}','${row.modify_time}','${row.enable}','${row.deleted}','${row.aduit}')"
    db_122_bumblebee.execute(insertSql)

})




//插入区域
db_35_mobileb2cdev.eachRow("SELECT pmt_id,area_id FROM sdb_pmt_area", { row ->
    if (row.area_id != 0) {
        db_122_bumblebee.execute("INSERT INTO pmt_area (id,pmt_id,area_id) VALUES (NULL ,${row.pmt_Id},${row.area_id})")
    } else {
        db_122_bumblebee.execute("INSERT INTO pmt_area SELECT NULL,${row.pmt_Id},code from pmt_region")
    }
})

*/
// 更新 附表
db_122_bumblebee.eachRow("SELECT product_bn FROM pmt_goods", { row ->
    def productSql = "SELECT goods_id,bn,id,name from ps_product WHERE bn = '${row.product_bn}'"
    def product = db_122_trailbreaker.firstRow(productSql)
    def updateSql = "UPDATE pmt_goods SET goods_id='${product.get("goods_id")}',product_id='${product.get("id")}',goods_name='${product.get("name")}' where product_bn='${row.product_bn}'"
    db_122_bumblebee.execute(updateSql)
})